package com.xmy.cultivate.util;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellReference;

public class ExcelUtils {


    /**
     * Excel 列号转数字
     *
     * @param excelNum Excel 列号
     * @return 数字
     */
    public static int excelColumnNumberToNumberConversion(String excelNum) {
        try {
            char[] chs = excelNum.toCharArray();
            int digit = 0;
            for (char ch : chs) {
                digit = digit * 26 + (ch - 'A' + 1);
            }
            return digit;
        } catch (Exception e) {
            return 26;
        }
    }

    /**
     * 数字转 Excel 列号
     *
     * @param digit 数字
     * @return Excel 列号
     */
    public static String convertNumbersToExcelColumnNumbers(int digit) {
        try {
            int len = 0, power = 1, pre = 0;
            for (; pre < digit; pre += power) {
                power *= 26;
                len++;
            }
            char[] excelNum = new char[len];
            digit -= pre - power;
            digit--;
            for (int i = 0; i < len; i++) {
                power /= 26;
                excelNum[i] = (char) (digit / power + 'A');
                digit %= power;
            }
            return String.valueOf(excelNum);
        } catch (Exception e) {
            return "S";
        }
    }

    /**
     * 标题通用单元格样式
     */
    public static CellStyle headCommonCellStyle(Workbook workbook) {
        CellStyle cellStyle = workbook.createCellStyle();
        Font font = workbook.createFont();
        font.setBold(true);
        font.setFontHeightInPoints((short) 16);
        cellStyle.setFont(font);
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        cellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());

        return cellStyle;
    }

    /**
     * 正文通用单元格样式
     */
    public static CellStyle bodyCommonCellStyle(Workbook workbook, IndexedColors foregroundColor) {
        CellStyle cellStyle = workbook.createCellStyle();
        Font font = workbook.createFont();
        font.setFontHeightInPoints((short) 13);
        cellStyle.setFont(font);
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);

        if (foregroundColor != null) {
            cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            cellStyle.setFillForegroundColor(foregroundColor.getIndex());
        }

        return cellStyle;
    }
}
